package com.duowan.reportengine.util;

import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionTemplate;

/**
 * 
 * 可以对List<Map>结构的数据，进行sql语句查询，底层使用H2数据库，支持任何的标准SQL语句
 * @author Irwin
 *
 */
public class ObjectSqlQueryUtil {
	public static String TABLE_NAME = "t";
	
	public static List<Map<String,Object>> query(final String sql,final List<Map<String,Object>> rows) {
		if(rows == null) return null;
		if(rows.isEmpty()) {
			return Collections.EMPTY_LIST;
		}
		
		final DataSource ds = getDataSource();
		TransactionTemplate tt = new TransactionTemplate(new DataSourceTransactionManager(ds));
		return tt.execute(new TransactionCallback<List<Map<String,Object>>>() {
			public List<Map<String,Object>> doInTransaction(TransactionStatus status) {
				JdbcTemplate jdbcTemplate = createTableAndInsertData(rows,ds);
				jdbcTemplate.execute("CREATE AGGREGATE IF NOT EXISTS collect_map FOR \"com.duowan.reportengine.h2.functions.CollectMapAggrFunction\"");
				jdbcTemplate.execute("CREATE ALIAS IF NOT EXISTS map FOR \"com.duowan.reportengine.h2.functions.H2Functions.string_map\"");
				jdbcTemplate.execute("CREATE ALIAS IF NOT EXISTS string_map FOR \"com.duowan.reportengine.h2.functions.H2Functions.string_map\"");
				jdbcTemplate.execute("CREATE ALIAS IF NOT EXISTS number_map FOR \"com.duowan.reportengine.h2.functions.H2Functions.number_map\"");
				jdbcTemplate.execute("CREATE ALIAS IF NOT EXISTS date_map FOR \"com.duowan.reportengine.h2.functions.H2Functions.date_map\"");
				jdbcTemplate.execute("CREATE ALIAS IF NOT EXISTS get_property FOR \"com.duowan.reportengine.h2.functions.H2Functions.get_property\"");
				
				return MapUtil.allMapKey2LowerCase(jdbcTemplate.queryForList(sql));
			}
		});
		
	}
	
	private static JdbcTemplate createTableAndInsertData(final List<Map<String, Object>> rows,final DataSource dataSource) {
		final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		String createTableSql = buildCreateTableSql(TABLE_NAME, rows);
		
		Map row = rows.get(0);
		String insertSql = buildInsertSql(TABLE_NAME,row);
		try {
			jdbcTemplate.execute(createTableSql);
			new NamedParameterJdbcTemplate(jdbcTemplate).batchUpdate(insertSql, rows.toArray(new Map[rows.size()]));
			return jdbcTemplate;
		}catch(Exception e) {
			throw new RuntimeException("error on createTableAndInsertData() createTableSql:"+createTableSql+" insertSql:"+insertSql,e) ;
		}
	}
	
	private static String buildCreateTableSql(String tableName, List<Map<String, Object>> rows) {
		for(int i = 0 ; i < rows.size(); i++) {
			Map row = rows.get(i);
			try {
				return buildCreateTableSql(tableName,row,true);
			}catch(IllegalArgumentException e) {
				//ignore
			}
		}
		return buildCreateTableSql(tableName,rows.get(0),false);
	}

	static String buildCreateTableSql(String tableName,Map<String,Object> map,boolean errorOnValueNull) {
		StringBuilder sql = new StringBuilder("create memory local temporary  table "+tableName+" (");
		boolean first = true;
		for(Map.Entry<String, Object> entry : map.entrySet()) {
			if(first) {
				first = false;
			}else {
				sql.append(",");
			}
			String key = entry.getKey();
			Object value = entry.getValue();
			if(errorOnValueNull && value == null) {
				throw new IllegalArgumentException("value is null by key:"+key);
			}
			String sqlType = getSqlType(value);
			sql.append(key + " " +sqlType);
			
		}
		
		return sql.append(" )").toString();
	}
	
	private static String getSqlType(Object value) {
		if(value instanceof String) {
			return "varchar(4000)";
		}else if(value instanceof Number) {
			return "NUMBER";
		}else if(value instanceof Date) {
			return "datetime";
		}else if(value instanceof Boolean) {
			return "bool";
		}else {
			return "OTHER";
		}
	}

	static String buildInsertSql(String tableName,Map<String,Object> map) {
		StringBuilder sql = new StringBuilder("insert into "+tableName+" (");
		boolean first = true;
		for(String key : map.keySet()) {
			if(first) {
				first = false;
			}else {
				sql.append(",");
			}
			sql.append(key);
		}
		
		sql.append(" ) values (");
		
		first = true;
		for(String key : map.keySet()) {
			if(first) {
				first = false;
			}else {
				sql.append(",");
			}
			sql.append(":").append(key);
		}
		sql.append(" )");
		return sql.toString();
	}
	
	private static DataSource getDataSource() {
		DriverManagerDataSource ds = new DriverManagerDataSource();
		ds.setDriverClassName("org.h2.Driver");
		ds.setPassword("sa");
		ds.setPassword("");
		ds.setUrl("jdbc:h2:mem:object_sql_query;MODE=MYSQL;DB_CLOSE_DELAY=-1");
		return ds;
	}
}
